﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Xml;
using MacomberMapSystem.Common.User_Interfaces.Database;
using System.Data.SQLite;
using System.Drawing;
using System.Data;
using System.IO;
using MacomberMapSystem.Common.Serialization;
using MacomberMapSystem.Common.Integration;
using MacomberMapSystem.Common.CIM;
using System.Drawing.Imaging;
using MacomberMapSystem.Common.Internals;
using System.Windows.Forms;
using System.Threading;
using MacomberMapSystem.Common.User_Interfaces.Mapping;
using MacomberMapSystem.Common.Integration.Blackstart;
#if DotNetOracle
using System.Data.OracleClient;
#else
    using Oracle.DataAccess.Client;
#endif
using System.Diagnostics;

namespace MacomberMapSystem.Common.Database
{
    /// <summary>
    /// This class is responsible for connecting to the MM Database, in order to retrieve model information and one-line handling
    /// </summary>
    public class MM_Database_Connector : MM_Serializable
    {
        #region Variable declarations
        /// <summary>The database to which the application is connected</summary>
        public DbConnection Database;

        /// <summary>The collection of database entries</summary>
        public Dictionary<String, MM_Database_Entry> DatabaseEntries = new Dictionary<string, MM_Database_Entry>();

        /// <summary>The current database</summary>
        public MM_Database_Entry CurrentDatabase;

        /// <summary>The path in which the model indexes are stored</summary>
        public string ModelIndexPath;

        /// <summary>Our data repository</summary>
        public MM_Repository Repository;

        /// <summary>Our collection of blackstart corridors</summary>
        public List<MM_Blackstart_Corridor> Blackstart_Corridors = new List<MM_Blackstart_Corridor>();

        /// <summary>Our collection of substations</summary>
        public MM_Indexed_Dictionary<CIM_RdfID, MM_Substation> Substations = new MM_Indexed_Dictionary<CIM_RdfID, MM_Substation>();

        /// <summary>Our collection of substations</summary>
        public Dictionary<UInt64, GIS_Substation> GISSubstations = new Dictionary<ulong, GIS_Substation>();

        /// <summary>Our collection of substations</summary>
        public Dictionary<UInt64, GIS_Line> GISLines = new Dictionary<ulong, GIS_Line>();

        /// <summary>Our collection of lines</summary>
        public MM_Indexed_Dictionary<CIM_RdfID, MM_Line> Lines = new MM_Indexed_Dictionary<CIM_RdfID, MM_Line>();

        /// <summary>Our collection of boundaries</summary>
        public MM_Indexed_Dictionary<String, MM_Boundary> Boundaries = new MM_Indexed_Dictionary<String, MM_Boundary>();

        /// <summary>Our state boundary</summary>
        public MM_Boundary StateBoundary;
        #endregion

        #region Initialization
        /// <summary>
        /// Initialize a blank db connector
        /// </summary>
        /// <param name="Repository"></param>
        public MM_Database_Connector(MM_Repository Repository)
            : base(null, null)
        {
            this.Repository = Repository;
        }

        /// <summary>
        /// Initialize a new Xml connector
        /// </summary>
        /// <param name="xDoc">The XML configuration</param>
        /// <param name="LoginForm">The login form</param>
        /// <param name="Repository">Our repository</param>
        public MM_Database_Connector(XmlDocument xDoc, frmLogin LoginForm, MM_Repository Repository)
            : base(xDoc.DocumentElement, null)
        {
            this.Repository = Repository;
            String Default = "";


            foreach (XmlElement xElem in xDoc.DocumentElement.ChildNodes.OfType<XmlElement>())
                if (xElem.Name == "MMDatabase" || xElem.Name == "MM_Database_Entry")
                {
                    DatabaseEntries.Add(xElem.Attributes["Name"].Value, new MM_Database_Entry(xElem, Repository));
                    if (xElem.HasAttribute("DefaultDatabase") && XmlConvert.ToBoolean(xElem.Attributes["DefaultDatabase"].Value))
                        Default = xElem.Attributes["Name"].Value;
                    else if (xElem.HasAttribute("Default") && XmlConvert.ToBoolean(xElem.Attributes["Default"].Value))
                        Default = xElem.Attributes["Name"].Value;
                }
            LoginForm.AddLoginOption("MM Database", "The database to which the Macomber Map system should be connected", Default, true, Connect, new List<String>(DatabaseEntries.Keys).ToArray());
        }
        #endregion

        #region Schema creation
          /// <summary>
        /// Create a new Oracle database schema to hold the MM loader data
        /// </summary>
        /// <param name="AdminUser">Administrator's user name</param>
        /// <param name="AdminPassword">Administrator's password</param>
        /// <param name="ServerName">Server TNS Name</param>
        /// <param name="UserName">New Schema name / User Name</param>
        /// <param name="UserPassword">New User password</param>
        /// <param name="SchemaSize">The current size of the database</param>
        /// <param name="MaxSize">The maximum size of the database</param>
        /// <param name="DbPath">The path to the Oracle database file</param>
        /// <param name="StepSize">The database incremental size</param>
        public static void CreateSchema(String AdminUser, String AdminPassword, String ServerName, String UserName, String UserPassword, String DbPath, int SchemaSize=4096, int MaxSize=5120, int StepSize=256)
        {            

       
                

            //Create our admin-level connection
            using (OracleConnection oConnAdmin = new OracleConnection(String.Format("Data Source={0}; User ID={1}; Password={2}", ServerName, AdminUser, AdminPassword)))
            {
                oConnAdmin.Open();

                //Run our Oracle commands to set up the database    
                List<String> Commands = new List<string>();
                Commands.Add(String.Format("CREATE USER {0} IDENTIFIED BY {1}", UserName, UserPassword));
                Commands.Add(String.Format("CREATE tablespace {0} datafile '{1}{0}.dbf' size {2}M autoextend on next {3}M maxsize {4}M extent management local", UserName, DbPath, SchemaSize, StepSize, MaxSize));
                Commands.Add(String.Format("GRANT CREATE SESSION TO {0}", UserName));
                Commands.Add(String.Format("GRANT CREATE TABLE TO {0}", UserName));
                Commands.Add(String.Format("GRANT CREATE TRIGGER TO {0}", UserName));
                Commands.Add(String.Format("GRANT CREATE SEQUENCE TO {0}", UserName));
                Commands.Add(String.Format("ALTER USER {0} QUOTA UNLIMITED ON {0}", UserName));
                Commands.Add(String.Format("ALTER USER {0} DEFAULT TABLESPACE {0}", UserName));
                foreach (String str in Commands)
                    using (OracleCommand oCmd = new OracleCommand(str, oConnAdmin))
                        oCmd.ExecuteNonQuery();

                //Close our admin connection, and establish our standard connection
                oConnAdmin.Close();
            }
        }


        #endregion
        #region Connection
        /// <summary>
        /// Connect to a target database
        /// </summary>
        /// <param name="Target"></param>
        /// <param name="ex"></param>
        /// <returns></returns>
        public bool Connect(String Target, out Exception ex)
        {
            if (!DatabaseEntries.TryGetValue(Target, out CurrentDatabase))
            {
                ex = new Exception("Unable to locate database entry for " + Target);
                return false;
            }
            else 
                try
                {                    
                    Database = CurrentDatabase.CreateConnection();                    
                    Database.Open();
                }
                catch (Exception ex2)
                {
                    ex = ex2;
                    return false;
                }
        
            //Check our list of models

            //Update our configuration based on the connection string
            Repository.xConfig = new XmlDocument();
            Object InVal;
            using (DbCommand dCmd = CreateCommand("SELECT VALUE FROM MM_CONFIGURATION WHERE PARAMETER=:0", Application.ProductName))
                InVal = dCmd.ExecuteScalar();
            if (InVal is String)
                Repository.xConfig.LoadXml((string)InVal);

                    
            //First, determine what entries need to be added and the new default
            List<MM_Database_Entry> AddedEntries = new List<MM_Database_Entry>();
            MM_Database_Entry NewDefault = null;
            Dictionary<String, MM_Database_Entry> DbEntries = LoadDatabaseEntries(Repository);            
            foreach (KeyValuePair<String, MM_Database_Entry> DbEntry in DbEntries)
            {
                if (!DatabaseEntries.ContainsKey(DbEntry.Key))                
                    AddedEntries.Add(DbEntry.Value);                    
                if (DbEntry.Value.DefaultDatabase)
                    NewDefault = DbEntry.Value;
            }
                
            //Then, determine which ones need to be removed
            List<MM_Database_Entry> RemovedEntries = new List<MM_Database_Entry>();
            MM_Database_Entry OldDefault = null;
            foreach (KeyValuePair<String, MM_Database_Entry> DbEntry in DatabaseEntries)
            {
                if (!DbEntries.ContainsKey(DbEntry.Key))
                    RemovedEntries.Add(DbEntry.Value);
                   if (DbEntry.Value.DefaultDatabase)
                    OldDefault = DbEntry.Value;
            }

            AddedEntries.Sort();
            RemovedEntries.Sort();
            if (AddedEntries.Count > 0 || RemovedEntries.Count > 0 || NewDefault != OldDefault)
            {
                StringBuilder sB = new StringBuilder();
                sB.AppendLine("The list of available MM databases have changed. Would you like to make the following changes?");
                sB.AppendLine();
                if (AddedEntries.Count > 0)
                    sB.AppendLine("Databases to add: " + MM_Database_Entry.ListEntries(AddedEntries, true));
                if (RemovedEntries.Count > 0)
                    sB.AppendLine("Databases to remove: " + MM_Database_Entry.ListEntries(RemovedEntries, true));
                if (NewDefault != OldDefault)
                {
                    sB.AppendLine("New default database: " + MM_Database_Entry.ListEntries(new MM_Database_Entry[] { NewDefault }, true));
                    sB.AppendLine("Old default database: " + MM_Database_Entry.ListEntries(new MM_Database_Entry[] { OldDefault }, true));
                }
                if (MessageBox.Show(sB.ToString(), Application.ProductName, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                {
                    //First, add and remove our entries
                    foreach (MM_Database_Entry Entry in AddedEntries)
                        DatabaseEntries.Add(Entry.Name, Entry);
                    foreach (MM_Database_Entry Entry in RemovedEntries)
                        DatabaseEntries.Remove(Entry.Name);
                    foreach (MM_Database_Entry Entry in DatabaseEntries.Values)
                        Entry.DefaultDatabase = Entry == NewDefault;

                    using (XmlTextWriter xW = new XmlTextWriter(Path.Combine(Application.StartupPath, Application.ProductName.Replace(" ", "").Replace("-", "") + "Configuration.xml"), new UTF8Encoding(false)))
                    {
                        xW.Formatting = Formatting.Indented;
                        xW.WriteStartDocument();
                        xW.WriteStartElement("Configuration");                        
                        xW.WriteAttributeString("ExportedOn", XmlConvert.ToString(DateTime.Now, XmlDateTimeSerializationMode.Unspecified));
                                xW.WriteAttributeString("ModelIndexPath", Repository.Db.ModelIndexPath);
                        foreach (MM_Database_Entry Entry in DatabaseEntries.Values)
                            Entry.WriteXmlForSerialization(xW);
                        xW.WriteEndDocument();
                    }

                    //Restart our application
                    ProcessStartInfo psi = new ProcessStartInfo(Environment.CommandLine);
                    psi.UseShellExecute = false;
                    Process.Start(psi);
                    Process.GetCurrentProcess().Kill();
                }
            }

            //Now, check for updates, and quit the program if we have any
            if (MM_Update_Checker.CheckForUpdates(this, Repository) == MM_Update_Checker.enumUpgradeAction.NoUpdatesAvailable)                
                ThreadPool.QueueUserWorkItem(new WaitCallback(MM_Update_Checker.RunUpdateThread), new object[] { this, Repository });
            ex = null;
            return true;

        }
        #endregion

        #region Database access
        /// <summary>
        /// Create an SQL command with assigned parameters
        /// </summary>
        /// <param name="SQLCommand"></param>
        /// <param name="Values"></param>
        /// <returns></returns>
        public DbCommand CreateCommand(String SQLCommand, params object[] Values)
        {
            if (Database.State != ConnectionState.Open)
                Database.Open();
            while (Database.State != ConnectionState.Open)
                System.Threading.Thread.Sleep(1000);
            DbCommand OutCmd = Database.CreateCommand();
            OutCmd.CommandText = SQLCommand;
            for (int a = 0; a < Values.Length; a++)
                OutCmd.Parameters.Add(CreateParameter(":" + a.ToString(), Values[a]));
            return OutCmd;
        }
        
        /// <summary>
        /// Create a parameter
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        public DbParameter CreateParameter(String Name, Object Value)
        {            
            
            DbParameter OutParameter;
            if (Database is OracleConnection)                                
#if DotNetOracle
                if (Value is UInt64 || Value is UInt32 || Value is UInt16 || Value is Single || Value is Double || Value is Int64 || Value is Int32 || Value is Int16 || Value.GetType().IsEnum)
                    OutParameter = new OracleParameter(Name, OracleType.Number);
                else if (Value is DateTime)
                    OutParameter = new OracleParameter(Name, OracleType.Timestamp);
                else if (Value is byte[])
                    OutParameter = new OracleParameter(Name, OracleType.Blob);
                else if (Value is XmlDocument)
                    OutParameter = new OracleParameter(Name, OracleType.Clob);
                else
                    OutParameter = new OracleParameter(Name, OracleType.VarChar);
#else
                if (Value is UInt64 || Value is UInt32 || Value is UInt16 || Value is Single || Value is Double || Value is Int64 || Value is Int32 || Value is Int16|| Value.GetType().IsEnum)
                    OutParameter = new OracleParameter(Name, OracleDbType.Decimal);
                else if (Value is DateTime)
                    OutParameter = new OracleParameter(Name, OracleDbType.TimeStamp);
                else if (Value is byte[])
                    OutParameter = new OracleParameter(Name, OracleDbType.Blob);
                else if (Value is XmlDocument)
                    OutParameter = new OracleParameter(Name, OracleDbType.Clob);
                else
                    OutParameter = new OracleParameter(Name, OracleDbType.Varchar2);
#endif
            else if (Database is SQLiteConnection)
                OutParameter = new SQLiteParameter();
            else
                throw new InvalidOperationException("Unknown database type");

            OutParameter.ParameterName = Name;
            if (Value is Image)
                using (MemoryStream mS = new MemoryStream())
                {
                    (Value as Image).Save(mS, ImageFormat.Png);
                    OutParameter.DbType = DbType.Binary;
                    OutParameter.Value = mS.ToArray();
                }
            else if (Value is XmlDocument)
            {
                using (MemoryStream mS = new MemoryStream())
                using (XmlTextWriter xW = new XmlTextWriter(mS, new UTF8Encoding(false)))
                {
                    xW.Formatting = Formatting.Indented;
                    (Value as XmlDocument).WriteTo(xW);
                    xW.Flush();
                    OutParameter.Value = new UTF8Encoding(false).GetString(mS.ToArray());
                }
            }
            else if (Value.GetType().IsEnum)
                OutParameter.Value = (int)Value;

            else
                OutParameter.Value = Value;
            return OutParameter;
        }

        /// <summary>
        /// Begin a transaction
        /// </summary>
        /// <returns></returns>
        public DbTransaction BeginTransaction()
        {
            return Database.BeginTransaction();
        }

        /// <summary>
        /// Create an SQL command with assigned parameters
        /// </summary>
        /// <param name="dTrans"></param>
        /// <param name="SQLCommand"></param>
        /// <param name="Values"></param>
        /// <returns></returns>
        public DbCommand CreateCommand(DbTransaction dTrans, String SQLCommand, params object[] Values)
        {
            DbCommand OutCmd = Database.CreateCommand();
            OutCmd.CommandText = SQLCommand;
            OutCmd.Transaction = dTrans;
            for (int a = 0; a < Values.Length; a++)
                OutCmd.Parameters.Add(CreateParameter(":" + a.ToString(), Values[a]));
            return OutCmd;
        }

        /// <summary>
        /// Return the count of rows
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public int CountRows(String TableName)
        {
            using (DbCommand dCmd = CreateCommand("SELECT COUNT(*) FROM " + TableName))
                return Convert.ToInt32(dCmd.ExecuteScalar());
        }


        /// <summary>
        /// Create a select SQL query to locate records with the specified parameters
        /// </summary>
        /// <param name="TableName"></param>        
        /// <returns></returns>
        public DbCommand SelectRows(String TableName)
        {
            return SelectRows(TableName, null, -1, -1, null);
        }


        /// <summary>
        /// Create a select SQL query to locate records with the specified parameters
        /// </summary>
        /// <param name="TableName">The table to be read</param>        
        /// <param name="Columns">The list of columns to be read</param>
        /// <returns></returns>
        public DbCommand SelectRows(String TableName, List<String> Columns)
        {
            return SelectRows(TableName, Columns, -1, -1, null);
        }


        /// <summary>
        /// Create a select SQL query to locate records with the specified parameters
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Values"></param>
        /// <returns></returns>
        public DbCommand SelectRows(String TableName, Dictionary<String, Object> Values)
        {
            return SelectRows(TableName, null, -1, -1, Values);
        }

        /// <summary>
        /// Create a select SQL query to locate records with the specified parameters
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Values"></param>
        /// <param name="Columns">The list of columns to be read</param>
        /// <returns></returns>
        public DbCommand SelectRows(String TableName, List<string> Columns, Dictionary<String, Object> Values)
        {
            return SelectRows(TableName, Columns, -1, -1, Values);
        }

        /// <summary>
        /// Create a select SQL query to locate records with the specified parameters
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="LowerLimit"></param>
        /// <param name="RowCount"></param>
        /// <param name="Values"></param>
        /// <param name="Columns">The list of columns to be read</param>
        /// <returns></returns>
        public DbCommand SelectRows(String TableName, List<String> Columns, int LowerLimit, int RowCount, Dictionary<String, Object> Values)
        {
            DbCommand OutCmd = Database.CreateCommand();
            StringBuilder sBd = new StringBuilder("SELECT ");
            if (Columns == null || Columns.Count == 0)
                sBd.Append("* FROM " + TableName);
            else
                sBd.Append(String.Join(",", Columns.ToArray()) + " FROM " + TableName);
            if (Values != null && Values.Count > 0)
            {
                bool AddedParam = false;
                foreach (KeyValuePair<String, Object> kvp in Values)
                {
                    String Verb = AddedParam ? " AND " : " WHERE ";
                    if (kvp.Value is DBNull)
                    {
                        sBd.Append(Verb + kvp.Key + " IS NULL");
                        AddedParam = true;
                    }
                    else
                    {
                        sBd.Append(Verb + kvp.Key + "=:" + kvp.Key);
                        AddedParam = true;
                        OutCmd.Parameters.Add(CreateParameter(kvp.Key, kvp.Value));                        
                    }
                }
            }

            if (LowerLimit != -1 && RowCount != -1)
                sBd.AppendFormat(" LIMIT {0},{1}", LowerLimit, RowCount);
            else if (LowerLimit != -1)
                sBd.AppendFormat(" LIMIT {0}", RowCount);
            OutCmd.CommandText = sBd.ToString();
            OutCmd.Prepare();
            return OutCmd;
        }

        /// <summary>
        /// Insert a row with the specified parameters into the target table
        /// </summary>
        /// <param name="TableName">The outgoing table name</param>
        /// <param name="Values">The collection of values</param>
        public int InsertRow(String TableName, Dictionary<String, Object> Values)
        {
            return InsertRow(null, TableName, Values);
        }


        /// <summary>
        /// Insert a row with the specified parameters into the target table
        /// </summary>
        /// <param name="dTrans">The transaction</param>
        /// <param name="TableName">The outgoing table name</param>
        /// <param name="Values">The collection of values</param>
        public int InsertRow(DbTransaction dTrans, String TableName, Dictionary<String, Object> Values)
        {
            using (DbCommand dCmd = Database.CreateCommand())
            {
                dCmd.Transaction = dTrans;
                StringBuilder sBd = new StringBuilder("INSERT INTO " + TableName + " (");
                StringBuilder sBd2 = new StringBuilder(") VALUES (");

                int CurValue = 0;
                foreach (KeyValuePair<String, Object> kvp in Values)
                {
                    if (dCmd is SQLiteCommand)
                        sBd.Append((CurValue == 0 ? "" : ", ") + "[" + kvp.Key + "]");
                    else if (dCmd is OracleCommand)
                        sBd.Append((CurValue == 0 ? "" : ", ") + "\"" + kvp.Key + "\"");
                    sBd2.Append((CurValue == 0 ? "" : ", ") + ":" + CurValue.ToString());
                    dCmd.Parameters.Add(CreateParameter(":" + CurValue.ToString(), kvp.Value));                    
                    CurValue++;
                }
                dCmd.CommandText = sBd.ToString() + sBd2.ToString() + ")";
                return dCmd.ExecuteNonQuery();
            }
        }
        
        /// <summary>
        /// Replace a row with the specified parameters into the target table
        /// </summary>
        /// <param name="TableName">The outgoing table name</param>
        /// <param name="Values">The collection of values</param>
        public int ReplaceRow(String TableName, Dictionary<String, Object> Values)
        {
            using (DbCommand dCmd = Database.CreateCommand())
            {
                StringBuilder sBd = new StringBuilder("REPLACE INTO " + TableName + " (");
                StringBuilder sBd2 = new StringBuilder(") VALUES (");

                int CurValue = 0;
                foreach (KeyValuePair<String, Object> kvp in Values)
                {
                    sBd.Append((CurValue == 0 ? "" : ", ") + "[" + kvp.Key + "]");
                    sBd2.Append((CurValue == 0 ? "" : ", ") + ":" + CurValue.ToString());
                    dCmd.Parameters.Add(CreateParameter(":" + CurValue.ToString(), kvp.Value));                    
                    CurValue++;
                }
                dCmd.CommandText = sBd.ToString() + sBd2.ToString() + ")";
                return dCmd.ExecuteNonQuery();
            }
        }

        #endregion

        #region Database Retrieval
        /// <summary>
        /// Load in our collection of database entries
        /// </summary>
        /// <param name="Repository"></param>
        /// <returns></returns>
        public Dictionary<String, MM_Database_Entry> LoadDatabaseEntries(MM_Repository Repository)
        {
            Dictionary<String, MM_Database_Entry> OutEntries = new Dictionary<string, MM_Database_Entry>();
            using (DbCommand dCmd = SelectRows("MM_DATABASE_ENTRY"))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                    OutEntries.Add(dRd["Name"].ToString(), new MM_Database_Entry(dRd, Repository));
            return OutEntries;
        }


        /// <summary>
        /// Load the collection of users from the database
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository">The data repository</param>
        public Dictionary<int, MM_Database_User> LoadUsers(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            LoginProcess.TaskTotal = CountRows("MM_Database_User");

            Dictionary<int, MM_Database_User> OutUsers = new Dictionary<int, MM_Database_User>();
            using (DbCommand dCmd = SelectRows("MM_Database_User"))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    MM_Database_User NewUser = new MM_Database_User(dRd, Repository);
                    UpdateUserCounts(NewUser);
                    OutUsers.Add(NewUser.ID, NewUser);
                    LoginProcess.TaskCurrent++;
                }
            return OutUsers;
        }

        /// <summary>
        /// Update the counts on items for a user
        /// </summary>
        /// <param name="User"></param>
        public void UpdateUserCounts(MM_Database_User User)
        {
            using (DbCommand dCmd2 = CreateCommand("SELECT COUNT(*) FROM MM_DATABASE_ASSIGNMENT WHERE AssignedTo=:0 AND COMPLETEDON IS NULL", User.ID))
                User.AssignedOneLines = Convert.ToInt32(dCmd2.ExecuteScalar());
            using (DbCommand dCmd2 = CreateCommand("SELECT SUM(ERRORS),SUM(WARNINGS) FROM MM_DATABASE_ASSIGNMENT WHERE AssignedTo=:0 AND COMPLETEDON IS NULL", User.ID))
            using (DbDataReader dRd = dCmd2.ExecuteReader())
                while (dRd.Read())
                    User.AssignedFixes = (dRd[0] is DBNull ? 0 : Convert.ToInt32(dRd[0])) + (dRd[1] is DBNull ? 0 : Convert.ToInt32(dRd[1]));

            using (DbCommand dCmd2 = CreateCommand("SELECT COUNT(*), SUM(ERRORS), SUM(WARNINGS) FROM MM_DATABASE_ASSIGNMENT WHERE CompletedBy=:0", User.ID))
            using (DbDataReader dRd = dCmd2.ExecuteReader())
                while (dRd.Read())
                {
                    User.CompletedOneLines = Convert.ToInt32(dRd[0]);
                    User.CompletedFixes = (dRd[1] is DBNull ? 0 : Convert.ToInt32(dRd[1])) + (dRd[2] is DBNull ? 0 : Convert.ToInt32(dRd[2]));
                }
        }



        /// <summary>
        /// Load the collection of models from the database
        /// </summary>
        /// <param name="OutModels">The data repository</param>
        /// <param name="Repository"></param>
        /// <param name="LoginProcess"></param>
        /// <returns></returns>        
        public void LoadModels(MM_Login_Process LoginProcess, MM_Repository Repository, Dictionary<int, MM_Database_Model> OutModels)
        {
            LoginProcess.TaskCurrent = 0;
            LoginProcess.TaskTotal = CountRows("MM_Database_Model");
            OutModels.Clear();
            
            using (DbCommand dCmd = CreateCommand("SELECT * FROM MM_Database_Model ORDER BY ID"))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    OutModels.Add(Convert.ToInt32(dRd["ID"]), new MM_Database_Model(dRd, Repository));
                    LoginProcess.TaskCurrent++;
                }
        }

        /// <summary>
        /// Load our KV levels from the database
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository">The repository</param>
        public void LoadKVLevels(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            LoginProcess.TaskTotal = CountRows("MM_KVLEVEL");            

            Data_Manager.KVLevels.Clear();
            using (DbCommand dCmd = SelectRows("MM_KVLEVEL"))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    Data_Manager.KVLevels.Add(Convert.ToInt32(dRd["ID"]), (string)dRd["Name"], new MM_KVLevel(dRd, Repository));
                    LoginProcess.TaskCurrent++;
                }
        }



        /// <summary>
        /// Load all one-line assignments into memory
        /// </summary>
        /// <param name="Repository">The data repository</param>
        /// <returns></returns>
        public Dictionary<int, MM_Database_Assignment> LoadAllOneLineAssignments(MM_Repository Repository)
        {
            Dictionary<int, MM_Database_Assignment> OutAssignments = new Dictionary<int, MM_Database_Assignment>();
            using (DbCommand dCmd = SelectRows("MM_Database_Assignment"))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                    OutAssignments.Add(Convert.ToInt32(dRd["ID"]), new MM_Database_Assignment(dRd, this, Repository));
            return OutAssignments;
        }

        /// <summary>
        /// Retrieve the list of synchroscopes
        /// </summary>
        /// <returns></returns>
        public Dictionary<CIM_RdfID, bool> Synchroscopes
        {
            get
            {
                if (_Synchroscopes == null)
                {
                    _Synchroscopes = new Dictionary<CIM_RdfID, bool>();
                    using (DbCommand dCmd = CreateCommand("SELECT rdfID FROM MM_Synchroscope" + (Repository.EditMode ? "_EDITOR" : "")))
                    using (DbDataReader dRd = dCmd.ExecuteReader())
                        while (dRd.Read())
                            if (dRd["rdfID"] is DBNull == false)
                            {
                                CIM_RdfID NewID = new CIM_RdfID((string)dRd["rdfID"]);

                                if (!_Synchroscopes.ContainsKey(NewID))
                                    _Synchroscopes.Add(NewID, true);
                            }
                }
                return _Synchroscopes;
            }
        }
        private Dictionary<CIM_RdfID, bool> _Synchroscopes;


        /// <summary>
        /// Retrieve the list of Synchrocheck_Relays
        /// </summary>
        /// <returns></returns>
        public Dictionary<CIM_RdfID, bool> Synchrocheck_Relays
        {
            get
            {
                    if (_Synchrocheck_Relays == null)
                    {
                        _Synchrocheck_Relays = new Dictionary<CIM_RdfID, bool>();
                        using (DbCommand dCmd = CreateCommand("SELECT rdfID FROM MM_Synchrocheck_Relay" + (Repository.EditMode ? "_EDITOR" : "")))
                        using (DbDataReader dRd = dCmd.ExecuteReader())
                            while (dRd.Read())
                                if (dRd["rdfID"] is DBNull == false)
                                {
                                    CIM_RdfID NewID = new CIM_RdfID((string)dRd["rdfID"]);

                                    if (!_Synchrocheck_Relays.ContainsKey(NewID))
                                        _Synchrocheck_Relays.Add(NewID, true);
                                }
                    }
                return _Synchrocheck_Relays;
            }
        }
        private Dictionary<CIM_RdfID, bool> _Synchrocheck_Relays;


        /// <summary>
        /// Upload our synchroscopes in its own thread
        /// </summary>
        /// <param name="state"></param>
        public void UploadSynchroscopes(object state)
        {
            object[] InVals = state as object[];
            UploadSynchroscopes(InVals[0] as string, InVals[1] as MM_Database_Model);
        }

        /// <summary>
        /// Upload our synchrocheck relays in its own thread
        /// </summary>
        /// <param name="state"></param>
        public void UploadSynchrocheckRelays(object state)
        {
            object[] InVals = state as object[];
            UploadSynchrocheckRelays(InVals[0] as string, InVals[1] as MM_Database_Model);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="Model"></param>
        public void UploadSynchrocheckRelays(String FileName, MM_Database_Model Model)
        {
            Exception ex;            
            if (Model.Substations.Count == 0)
            {
                MM_Login_Process NewProcess = new MM_Login_Process("Load substations", Model.Repository.LoadModels);
                Model.LoadSubstations(NewProcess, out ex);
            }

            //First, clear our synchroscope directory
            using (DbCommand dCmd = CreateCommand("DELETE FROM MM_Synchrocheck_Relay" + (Repository.EditMode ? "_EDITOR" : "")))
                dCmd.ExecuteNonQuery();

            //Now, read our file line by line            
            List<CIM_Element> OutElems = new List<CIM_Element>();
            Dictionary<String, int> MismatchedText = new Dictionary<string, int>();
            StringBuilder Responses = new StringBuilder();

            String InLine;
            using (StreamReader sRd = new StreamReader(FileName))
                while (!String.IsNullOrEmpty(InLine = sRd.ReadLine()))
                {
                    string[] splStr = InLine.Split('.');
                    CIM_Element ElemToAdd = null;
                    if (splStr.Length == 1 || String.IsNullOrEmpty(splStr[1]))
                        if (Model.Substations.TryGetValue(splStr[0], out ElemToAdd))
                            OutElems.Add(ElemToAdd);
                        else
                            MismatchedText.Add(InLine, 0);
                    else if (splStr.Length == 2)
                        if (!Model.Substations.TryGetValue(splStr[0], out ElemToAdd))
                            MismatchedText.Add(InLine, 1);
                        else
                        {
                            CIM_Element FoundElem = null;
                            foreach (CIM_Element Elem in ElemToAdd.Elements)
                                if (FoundElem == null && (Elem.TypeName == "cim:Breaker" || Elem.TypeName == "cim:Disconnector") && Elem.Name.Equals(splStr[1], StringComparison.CurrentCultureIgnoreCase))
                                    FoundElem = Elem;
                            if (FoundElem == null)
                                MismatchedText.Add(InLine, 2);
                            else
                                OutElems.Add(FoundElem);
                        }
                    Application.DoEvents();
                }
            //Now, add in our elements
            foreach (CIM_Element Elem in OutElems)
                using (DbCommand dCmd = CreateCommand("INSERT INTO MM_Synchrocheck_Relay" + (Repository.EditMode ? "_EDITOR" : "") + " (rdfID, Substation, Name, ElemType, ID) VALUES (:0, :1, :2, :3, SEQ_MM_SYNCHROCHECK_RELAY" +  (Repository.EditMode ? "_EDITOR" : "") + ".NEXTVAL)", Elem.rdfID.ToString(), (Elem.Substation != null ? Elem.Substation.Name : ""), Elem.Name, Elem.ElemType))
                    dCmd.ExecuteNonQuery();

            if (Responses.Length > 0)
            {
                MessageBox.Show(Responses.ToString(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
                Clipboard.SetText(Responses.ToString());
            }
        }

        /// <summary>
        /// Upload new synchroscopes based on a model
        /// </summary>
        /// <param name="FileName"></param>
        /// <param name="Model"></param>
        public void UploadSynchroscopes(String FileName, MM_Database_Model Model)
        {
            Exception ex;
            if (Model.Substations.Count == 0)
            {
                MM_Login_Process LoginProcess = new MM_Login_Process("Load Substations", Model.LoadSubstations);
                Model.LoadSubstations(LoginProcess, out ex);
            }

            //First, clear our synchroscope directory
            using (DbCommand dCmd = CreateCommand("DELETE FROM MM_SYNCHROSCOPE" + (Repository.EditMode ? "_EDITOR" : "")))
                dCmd.ExecuteNonQuery();

            //Now, read our file line by line            
            List<CIM_Element> OutElems = new List<CIM_Element>();
            Dictionary<String, int> MismatchedText = new Dictionary<string, int>();
            StringBuilder Responses = new StringBuilder();

            String InLine;
            using (StreamReader sRd = new StreamReader(FileName))
                while (!String.IsNullOrEmpty(InLine = sRd.ReadLine()))
                {
                    string[] splStr = InLine.Split('.');
                    CIM_Element ElemToAdd = null;
                    if (splStr.Length == 1 || String.IsNullOrEmpty(splStr[1]))
                        if (Model.Substations.TryGetValue(splStr[0], out ElemToAdd))
                            OutElems.Add(ElemToAdd);
                        else
                            MismatchedText.Add(InLine, 0);
                    else if (splStr.Length == 2)
                        if (!Model.Substations.TryGetValue(splStr[0], out ElemToAdd))
                            MismatchedText.Add(InLine, 1);
                        else
                        {
                            CIM_Element FoundElem = null;
                            foreach (CIM_Element Elem in ElemToAdd.Elements)
                                if (FoundElem == null && (Elem.TypeName == "cim:Breaker" || Elem.TypeName == "cim:Disconnector") && Elem.Name.Equals(splStr[1], StringComparison.CurrentCultureIgnoreCase))
                                    FoundElem = Elem;
                            if (FoundElem == null)
                                MismatchedText.Add(InLine, 2);
                            else
                                OutElems.Add(FoundElem);
                        }
                    Application.DoEvents();
                }
            //Now, add in our elements
            foreach (CIM_Element Elem in OutElems)
                using (DbCommand dCmd = CreateCommand("INSERT INTO MM_SYNCHROSCOPE" + (Repository.EditMode ? "_EDITOR" : "") + " (rdfID, Substation, Name, ElemType) VALUES (:0, :1, :2, :3)", Elem.rdfID.ToString(), (Elem.Substation != null ? Elem.Substation.Name : ""), Elem.Name, Elem.ElemType))
                    dCmd.ExecuteNonQuery();

            if (Responses.Length > 0)
            {
                MessageBox.Show(Responses.ToString(), Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Information);
                Clipboard.SetText(Responses.ToString());
            }


        }

        /// <summary>
        /// Load all one-line assignments into memory
        /// </summary>
        /// <param name="Repository">The data repository</param>
        /// <returns></returns>
        public Dictionary<int, MM_Database_Assignment> LoadActiveOneLineAssignments(MM_Repository Repository)
        {
            Dictionary<int, MM_Database_Assignment> OutAssignments = new Dictionary<int, MM_Database_Assignment>();
            using (DbCommand dCmd = SelectRows("MM_Database_Assignment", GenerateParams("CompletedOn", DBNull.Value)))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                    OutAssignments.Add(Convert.ToInt32(dRd["ID"]), new MM_Database_Assignment(dRd, this, Repository));
            return OutAssignments;
        }

        /// <summary>
        /// Load all one-line assignments for a particular user in memory
        /// </summary>
        /// <param name="Repository"></param>
        /// <param name="User"></param>
        /// <returns></returns>
        public Dictionary<int, MM_Database_Assignment> LoadActiveOneLineAssignments(MM_Repository Repository, MM_Database_User User)
        {
            Dictionary<int, MM_Database_Assignment> OutAssignments = new Dictionary<int, MM_Database_Assignment>();
            using (DbCommand dCmd = CreateCommand("SELECT ONELINE, MODEL, ASSIGNEDTO, ASSIGNEDON, COMPLETEDON, CHECKEDOUTON,ID,COMPLETEDBY,EXPORTEDON, ERRORS, WARNINGS, ONELINETYPE, ONELINENAME FROM MM_DATABASE_ASSIGNMENT WHERE COMPLETEDON IS NULL AND ASSIGNEDTO=:0", User.ID))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                    OutAssignments.Add(Convert.ToInt32(dRd["ID"]), new MM_Database_Assignment(dRd, this, Repository));
            return OutAssignments;
        }


        /// <summary>
        /// Generate a dictionary of string/object for parameters
        /// </summary>
        /// <param name="inValues"></param>
        /// <returns></returns>
        public Dictionary<String, Object> GenerateParams(params object[] inValues)
        {
            Dictionary<String, object> OutObj = new Dictionary<string, object>();
            for (int a = 0; a < inValues.Length; a += 2)
                OutObj.Add((string)inValues[a], inValues[a + 1]);
            return OutObj;
        }
        #endregion

        /// <summary>
        /// Return a new list of strings
        /// </summary>
        /// <param name="ListParams"></param>
        /// <returns></returns>
        public List<String> GenerateList(params string[] ListParams)
        {
            return new List<String>(ListParams);
        }

        /// <summary>
        /// Load just the image for a one-line
        /// </summary>
        /// <param name="ID">The ID of the one-line to be read</param>
        /// <returns></returns>
        public Bitmap LoadOneLineImage(int ID)
        {
            using (DbCommand dCmd = SelectRows("MM_Database_Oneline", GenerateList("IMAGE"), GenerateParams("ID", ID)))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                    if (dRd["Image"] is Byte[])
                        using (MemoryStream mS = new MemoryStream((byte[])dRd["Image"]))
                            return (Bitmap)Bitmap.FromStream(mS);
                    else
                    {
                        Bitmap OutBitmap = new Bitmap(200, 200);
                        using (Graphics g = Graphics.FromImage(OutBitmap))
                        {
                            g.DrawLine(Pens.Red, 0, 0, 200, 200);
                            g.DrawLine(Pens.Red, 200, 0, 0, 200);
                            return OutBitmap;
                        }
                    }
            throw new InvalidOperationException();
        }

        /// <summary>
        /// Load a full one-line into memory
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Repository">The repository that may contain additionally needed elements</param>
        /// <returns></returns>
        public MM_Database_OneLine LoadOneLine(int ID, MM_Repository Repository)
        {
            using (DbCommand dCmd = SelectRows("MM_Database_Oneline", GenerateParams("ID", ID)))
            using (DbDataReader dRd = dCmd.ExecuteReader(CommandBehavior.SingleRow))
                while (dRd.Read())
                    return new MM_Database_OneLine(dRd, Repository);
            throw new InvalidOperationException();
        }

        /// <summary>
        /// Build an adapter out of a command
        /// </summary>
        /// <param name="dbCommand"></param>
        /// <returns></returns>
        public DbDataAdapter CreateAdapter(DbCommand dbCommand)
        {
            return new OracleDataAdapter(dbCommand as OracleCommand);
        }

        /// <summary>
        /// Back up a one-line
        /// </summary>
        /// <param name="OneLine"></param>
        /// <param name="User"></param>
        public void BackupOneLine(MM_Database_OneLine OneLine, MM_Database_User User)
        {
            //First, load our XML
            String OldXml;
            using (DbCommand dCmd = CreateCommand("SELECT DOCUMENT FROM MM_DATABASE_ONELINE WHERE ID=:0", OneLine.ID))
                OldXml = (string)dCmd.ExecuteScalar();

            //Now, back it up.
            using (DbCommand dCmd = CreateCommand("INSERT INTO MM_ONELINE_BACKUP (ID,ONELINE,EDITEDBY,DOCUMENT,SAVEDON) VALUES (SEQ_MM_ONELINE_BACKUP.NextVal, :0, :1, :2, :3)", OneLine.ID, User.ID, OldXml, DateTime.Now))
                dCmd.ExecuteNonQuery();
        }

        /// <summary>
        /// Load in our collection of substations
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository"></param>
        public void LoadSubstations(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            String TableName = "MM_SUBSTATION" + (Repository.EditMode ? "_EDITOR" : "");
            LoginProcess.TaskTotal = CountRows(TableName);

            using (DbCommand dCmd = SelectRows(TableName))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    Substations.Add(new CIM_RdfID((string)dRd["rdfID"]), new MM_Substation(dRd, Repository));
                    LoginProcess.TaskCurrent++;
                }
        }

        /// <summary>
        /// Load in our collection of GIS substations
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository"></param>
        public void LoadGISSubstations(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            String TableName = "GIS_Substation";
            LoginProcess.TaskTotal = CountRows(TableName);

            using (DbCommand dCmd = SelectRows(TableName))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    GISSubstations.Add(Convert.ToUInt64(dRd["Sub_ID"]), new GIS_Substation(dRd,Repository));
                    LoginProcess.TaskCurrent++;
                }
        }

        /// <summary>
        /// Load in our collection of blackstart corridors
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository"></param>
        public void LoadBlackstartCorridors(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            String TableName = "MM_BLACKSTART_CORRIDOR" + (Repository.EditMode ? "_EDITOR" : "");
            LoginProcess.TaskTotal = CountRows(TableName);

            using (DbCommand dCmd = SelectRows(TableName)) 
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    Blackstart_Corridors.Add(new MM_Blackstart_Corridor(dRd, null, Repository));
                    LoginProcess.TaskCurrent++;
                }
            Blackstart_Corridors.Sort();
        }


        /// <summary>
        /// Load in our collection of lines
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository"></param>
        public void LoadLines(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            String TableName = "MM_Line" + (Repository.EditMode ? "_EDITOR" : "");
            LoginProcess.TaskTotal = CountRows(TableName);

            using (DbCommand dCmd = SelectRows(TableName))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    Lines.Add(new CIM_RdfID((string)dRd["rdfID"]), new MM_Line(dRd, Repository));
                    LoginProcess.TaskCurrent++;
                }            
        }

        /// <summary>
        /// Load in our collection of GIS provided lines
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository"></param>
        public void LoadGISLines(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            String TableName = "GIS_Line";
            LoginProcess.TaskTotal = CountRows(TableName);

            using (DbCommand dCmd = SelectRows(TableName))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    GISLines.Add(Convert.ToUInt64(dRd["Line_ID"]), new GIS_Line(dRd,Repository));
                    LoginProcess.TaskCurrent++;
                }            
        }


        /// <summary>
        /// Load in our collection of boundaries
        /// </summary>
        /// <param name="LoginProcess"></param>
        /// <param name="Repository"></param>
        public void LoadBoundaries(MM_Login_Process LoginProcess, MM_Repository Repository)
        {
            LoginProcess.TaskCurrent = 0;
            LoginProcess.TaskTotal = CountRows("MM_Boundary");
            using (DbCommand dCmd = CreateCommand("SELECT * FROM MM_BOUNDARY"))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    if ((string)dRd["Name"] == "STATE")
                        StateBoundary = new MM_Boundary(dRd, Repository);
                    else
                        Boundaries.Add((string)dRd["Name"], new MM_Boundary(dRd, Repository));
                    LoginProcess.TaskCurrent++;
                }
        }


        /// <summary>
        /// Load a map tile from the Oracle database
        /// </summary>
        /// <param name="mapType">The map type to be used</param>
        /// <param name="TileXY">The coordinates of the tile</param>
        /// <param name="ZoomLevel">The zoom level of the map tile</param>
        /// <param name="Tile">The tile, if it can be retrieved</param>
        /// <returns></returns>
        public bool LoadMapTile(MM_MapTile.MapType mapType, Point TileXY, int ZoomLevel, out Bitmap Tile)
        {
            using (DbCommand dCmd = CreateCommand("SELECT TILE FROM MM_MAPTILE WHERE TYPE=:0 AND X=:1 and Y=:2 AND ZOOM=:3", (int)mapType, TileXY.X, TileXY.Y, ZoomLevel))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                if (dRd.Read())
                {
                    using (MemoryStream inMs = new MemoryStream((byte[])dRd["TILE"]))
                        Tile = new Bitmap(inMs);
                    return true;
                }
                else
                {
                    Tile = null;
                    return false;
                }
        }

        /// <summary>
        /// Save a map tile
        /// </summary>
        /// <param name="NewTile"></param>
        public void SaveMapTile(MM_MapTile NewTile)
        {
            using (DbCommand dCmd = CreateCommand("UPDATE MM_MAPTILE SET TILE=:0 WHERE TYPE=:1 AND X=:2 AND Y=:3 AND ZOOM=:4",NewTile.Tile, (int)NewTile.mapType, NewTile.TileXY.X, NewTile.TileXY.Y, NewTile.ZoomLevel))
                if (dCmd.ExecuteNonQuery() == 0)
                {
                    dCmd.CommandText = "INSERT INTO MM_MAPTILE (TILE, TYPE, X, Y, ZOOM, ID) VALUES (:0,:1,:2,:3,:4, SEQ_MM_MAPTILE.NextVal)";
                    dCmd.ExecuteNonQuery();
                }
        }


        /// <summary>
        /// Determine the last assignment for a one-line
        /// </summary>
        /// <param name="OneLine"></param>
        /// <returns></returns>
        public MM_Database_Assignment GetLastAssignment(MM_Database_OneLine OneLine)
        {
            MM_Database_Assignment FoundAssignment = null;
            using (DbCommand dCmd = CreateCommand("SELECT ONELINE, MODEL, ASSIGNEDTO, ASSIGNEDON, COMPLETEDON, CHECKEDOUTON,ID,COMPLETEDBY,EXPORTEDON, ERRORS, WARNINGS, ONELINETYPE, ONELINENAME FROM MM_DATABASE_ASSIGNMENT WHERE COMPLETEDON IS NOT NULL AND ONELINE=:0", OneLine.ID))
            using (DbDataReader dRd = dCmd.ExecuteReader())
                while (dRd.Read())
                {
                    MM_Database_Assignment Assignment = new MM_Database_Assignment(dRd, this, Repository);
                    if (FoundAssignment==null || Assignment.CompletedOn > FoundAssignment.CompletedOn)
                        FoundAssignment=Assignment;
                }
            return FoundAssignment;
        }
    }
}